unit _db;
interface
uses PDO, PDOStatement, PDOClasses, sysUtils, StdCtrls, ComCtrls, Dialogs
  ,Grids, Classes
  ,_nodes, _config, _types, _data
  ;

  type TSql2lDB = class(TPDO)
    procedure treeLoadItems(var T:TTreeView);
    procedure treeLoadObjects(const parentNode:TTreeNode;dbName:String);
    procedure treeLoadTables(const parentNode:TTreeNode;dbName:String);
    procedure treeLoadViews(const parentNode:TTreeNode;dbName:String);
    procedure treeLoadEvents(const parentNode:TTreeNode;dbName:String);
    procedure treeLoadTriggers(const parentNode:TTreeNode;dbName:String);
    procedure treeLoadProcedures(const parentNode:TTreeNode;dbName:String);
    procedure treeLoadFunctions(const parentNode:TTreeNode;dbName:String);

    procedure loadDataFromSQL(sql:string;const dest:TAppDataGrid;columns_def:string = '');
  end;

var
  db:TSql2lDB;

implementation


procedure TSql2lDB.treeLoadItems(var T:TTreeView);
var
    i:integer;
    n:TTreeNode;
    stmt: IPDOStatement;
    row: TPDORowNative;
    d:PNodeData;
begin
  try
    row:=TPDORowNative.Create;
    stmt := query_as_is('SHOW DATABASES');
    new(d); d^.nodeType:=ntDatabase;
    while (stmt.fetch(row)) do
    begin
      n := T.Items.AddObject(nil,row.column[0].asString,d);
    end;
  except
    on E:Exception do ShowMessage(E.Message);
  end;
  row.Free;
end;

procedure TSql2lDB.treeLoadObjects(const parentNode:TTreeNode;dbName:String);
var n:TTreeNode;
begin
  parentNode.Owner.BeginUpdate;
  treeLoadTables(parentNode.Owner.AddChild(parentNode,'Tables'),dbName);
  treeLoadViews(parentNode.Owner.AddChild(parentNode,'Views'),dbName);
  treeLoadProcedures(parentNode.Owner.AddChild(parentNode,'Procedures'),dbName);
  treeLoadFunctions(parentNode.Owner.AddChild(parentNode,'Functions'),dbName);
  treeLoadTriggers(parentNode.Owner.AddChild(parentNode,'Triggers'),dbName);
  treeLoadEvents(parentNode.Owner.AddChild(parentNode,'Events'),dbName);
  parentNode.Owner.EndUpdate;
  //}
end;

procedure TSql2lDB.treeLoadTables(const parentNode:TTreeNode;dbName:String);
var stmt: IPDOStatement;
    row: TPDORowNative;
    n:TTreeNode;
    d:PNodeData;
begin
  stmt := query_as_is(format('SELECT table_name FROM information_schema.tables WHERE table_schema = ''%s'' and (table_type = ''BASE TABLE'' or table_type = ''SYSTEM VIEW'')',[dbName]));
  row:=TPDORowNative.Create;
  while (stmt.fetch(row)) do
  begin
    n:=TTreeNode.Create(nil);
    new(d); d^.nodeType:=ntTable;
    parentNode.Owner.AddChildObject(parentNode,row.column[0].asString,d);
  end;
  row.Free;
end;

procedure TSql2lDB.treeLoadViews(const parentNode:TTreeNode;dbName:String);
var stmt: IPDOStatement;
    row: TPDORowNative;
    n:TTreeNode;
    d:PNodeData;
begin
  stmt := query_as_is(format('SELECT table_name FROM information_schema.views WHERE table_schema = ''%s''',[dbName]));
  row:=TPDORowNative.Create;
  while (stmt.fetch(row)) do
  begin
    new(d); d^.nodeType:=ntView;
    parentNode.Owner.AddChildObject(parentNode,row.column[0].asString,d);
  end;
  row.Free;
end;

procedure TSql2lDB.treeLoadProcedures(const parentNode:TTreeNode;dbName:String);
var stmt: IPDOStatement;
    row: TPDORowNative;
    n:TTreeNode;
    d:PNodeData;
begin
  stmt := query_as_is(format('SELECT routine_name FROM information_schema.routines WHERE routine_schema = ''%s'' AND routine_type = ''PROCEDURE''',[dbName]));
  row:=TPDORowNative.Create;
  while (stmt.fetch(row)) do
  begin
    new(d); d^.nodeType:=ntProcedure;
    parentNode.Owner.AddChildObject(parentNode,row.column[0].asString,d);
  end;
  row.Free;
end;

procedure TSql2lDB.treeLoadFunctions(const parentNode:TTreeNode;dbName:String);
var stmt: IPDOStatement;
    row: TPDORowNative;
    n:TTreeNode;
    d:PNodeData;
begin
  stmt := query_as_is(format('SELECT routine_name FROM information_schema.routines WHERE routine_schema = ''%s'' AND routine_type = ''FUNCTION''',[dbName]));
  row:=TPDORowNative.Create;
  while (stmt.fetch(row)) do
  begin
    new(d); d^.nodeType:=ntFunction;
    parentNode.Owner.AddChildObject(parentNode,row.column[0].asString,d);
  end;
  row.Free;
end;



procedure TSql2lDB.treeLoadTriggers(const parentNode:TTreeNode;dbName:String);
var stmt: IPDOStatement;
    row: TPDORowNative;
    n:TTreeNode;
    d:PNodeData;
begin
  stmt := query_as_is(format('SELECT trigger_name FROM information_schema.triggers WHERE trigger_schema = ''%s''',[dbName]));
  row:=TPDORowNative.Create;
  while (stmt.fetch(row)) do
  begin
    new(d); d^.nodeType:=ntTrigger;
    parentNode.Owner.AddChildObject(parentNode,row.column[0].asString,d);
  end;
  row.Free;
end;

procedure TSql2lDB.treeLoadEvents(const parentNode:TTreeNode;dbName:String);
var stmt: IPDOStatement;
    row: TPDORowNative;
    n:TTreeNode;
    d:PNodeData;
begin
  stmt := query_as_is(format('SELECT event_name FROM information_schema.events WHERE event_schema = ''%s''',[dbName]));
  row:=TPDORowNative.Create;
  while (stmt.fetch(row)) do
  begin
    new(d); d^.nodeType:=ntEvent;
    parentNode.Owner.AddChildObject(parentNode,row.column[0].asString,d);
  end;
  row.Free;
end;

procedure TSql2lDB.loadDataFromSQL(sql:string;const dest:TAppDataGrid;columns_def:string = '');
var stmt: IPDOStatement;
    rowKeyPair: TPDORowKeyStrings;
    c,i,maxWidth:integer;
    n,v:string;
    columnsList:TStringList;
begin
  try
    columnsList:=TStringList.Create();
    if (columns_def <> '') then
    begin
      columnsList.commaText := columns_def;
    end;
    stmt := query_as_is(sql);
    rowKeyPair:=TPDORowKeyStrings.Create;
    if (stmt.columnCount = 0) then raise(Exception.Create('Query error'));
    with dest as TAppDataGrid do
    begin
      RowCount := 2;
      FixedCols :=0;
      if (columns_def = '') then ColCount := stmt.columnCount else ColCount := columnsList.count;
      FixedRows :=1;
    end;
    while (stmt.fetch(rowKeyPair)) do
    begin
      c := 0;
      for i := 0 to stmt.columnCount-1 do
      begin
        rowKeyPair.GetNameValue(i, n, v);
        if ((columns_def = '') or (columnsList.Values[n] <> '')) then
        begin
          with dest as TAppDataGrid do
          begin
            if (RowCount = 2) then
            begin
              if (columns_def <> '') then n := columnsList.Values[n];
              Cells[c, 0] := n;
            end;
            Cells[c, RowCount - 1] := v;
          end;
          inc(c);
        end;
      end;
      with dest as TAppDataGrid do RowCount:=RowCount+1;
    end;
    with dest as TAppDataGrid do
    begin
      RowCount:=RowCount-1;
      AutoSizeColumns;
      maxWidth:=_config.data.getObjectFor('datagrid_column_width_max',DATAGRID_COLUMN_WIDTH_MAX_DEFAULT).asInt();
      if (maxWidth < 10) then maxWidth := 10;
      for i:=0 to dest.ColCount-1 do
      begin
        if (dest.ColWidths[i] > maxWidth) then dest.ColWidths[i]:= maxWidth;


      end;
    end;
  finally
    columnsList.Free;
  end;
end;

initialization
  db:=TSql2lDB.create;

finalization
  db.Free;

end.
